﻿CREATE PROCEDURE [maint].[SendFailedJobNotifications]
@HostJobId UNIQUEIDENTIFIER
AS
BEGIN
	DECLARE @LastQueryTime datetime
	SET @LastQueryTime = util.GetJobLastRunTime(@HostJobId, NULL)

	DECLARE JOB_CUR CURSOR FAST_FORWARD FOR(
	SELECT
		jobs.[name]
		,util.GetDateFromInt(history.[run_date],history.[run_time]) AS [Time]
		,history.[run_duration]
		,history.[message]
	FROM [msdb].dbo.sysjobs AS jobs
	INNER JOIN [msdb].dbo.sysjobhistory AS history
		ON (history.[job_id] = jobs.[job_id])
		AND(history.[step_id] = 0)
		AND(history.[run_status] <> 1)
		AND(util.GetDateFromInt(history.[run_date],history.[run_time]) > @LastQueryTime)
	) ORDER BY [Time]

	OPEN JOB_CUR
	DECLARE @Name nvarchar(128), @Time datetime, @Duration int, @Message nvarchar(1024)
	FETCH NEXT FROM JOB_CUR INTO @Name, @Time, @Duration, @Message
	WHILE(@@FETCH_STATUS = 0)
		BEGIN
			DECLARE @MailSubject nvarchar(256)
			SET @MailSubject = N'SQL Server Job System: ''' + @Name + N''' completed on \\' + @@SERVERNAME

			DECLARE @MailMessage nvarchar(4000)
			SET @MailMessage = N'JOB RUN:' + NCHAR(9) + N'''' + @Name + N''''
				+ N' was run on ' + CONVERT(nchar(10),@Time,23) + N' at ' + CONVERT(nchar(10),@Time,108)
				+ NCHAR(10) + N'DURATION:' + NCHAR(9) + util.GetDurationFromInt(@Duration)
				+ NCHAR(10) +  N'STATUS:' + NCHAR(9) + NCHAR(9) + N'Failed'
				+ NCHAR(10) +  N'MESSAGES:' + NCHAR(9) + @Message

			EXEC util.JobFailureNotification @MailSubject, @MailMessage

			FETCH NEXT FROM JOB_CUR INTO @Name, @Time, @Duration, @Message
		END
	CLOSE JOB_CUR
	DEALLOCATE JOB_CUR

	RETURN 0
END